package com.art.sunflower.util;

import com.art.sunflower.pojo.Classroom;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class UploadUtils {

    private static final String XLS = "xls";
    private static final String XLSK = "xlsx";  //定义全局的常量值

    private String getCellValue(Cell cell) {
        String value = "";
        if (cell != null) {
            //以下是判断数据的类型
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC://数字
                    value = cell.getNumericCellValue() + "";
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        Date date = cell.getDateCellValue();
                        if (date != null) {
                            value = new SimpleDateFormat("yyyy-MM-dd").format(date);
                        } else {
                            value = "";
                        }
                    } else {
                        value = new DecimalFormat("0").format(cell.getNumericCellValue());
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING: //字符串
                    value = cell.getStringCellValue();
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN: //boolean
                    value = cell.getBooleanCellValue() + "";
                    break;
                case HSSFCell.CELL_TYPE_FORMULA: //公式
                    value = cell.getCellFormula() + "";
                    break;
                case HSSFCell.CELL_TYPE_BLANK: //空值
                    value = "";
                    break;
                case HSSFCell.CELL_TYPE_ERROR: //故障
                    value = "非法字符";
                    break;
                default:
                    value = "未知类型";
                    break;
            }
        }
        return value.trim();
    }

    private Workbook judgeKind(MultipartFile file) throws Exception {
        Workbook workbook = null;
        String fileName = file.getOriginalFilename();
        if (fileName.endsWith(XLS)) {
            try {
                workbook = new HSSFWorkbook(file.getInputStream());
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else if (fileName.endsWith(XLSK)) {
            try {
                workbook = new XSSFWorkbook(file.getInputStream());
            } catch (Exception e) {
                e.printStackTrace();
            }
        } else {
            throw new Exception("文件不是Excel文件");
        }
        return workbook;
    }


    //学生
//    public List<StudentExample> studentImportExcel(MultipartFile file) throws Exception {
//        List<StudentExample> list = new ArrayList<StudentExample>();
//        String fileName = file.getOriginalFilename();
//        Workbook workbook = judgeKind(file);
//        Sheet sheet = workbook.getSheet("Sheet1");
//        int rows = sheet.getLastRowNum();//指定行数。一共多少+
//        if (rows == 0) {
//            throw new Exception("请填写行数");
//        }
//        for (int i = 1; i < rows + 1; i++) {
//            //读取左上端单元格
//            Row row = sheet.getRow(i);
//            //行不为空
//            if (row != null) {
//                //读取cell
//                StudentExample studentExample = new StudentExample();
//                //学号
//               studentExample.setUsername(getCellValue(row.getCell(0)));
//                //角色
//                studentExample.setRole(getCellValue(row.getCell(1)));
//                //密码
//                String pwd = getCellValue(row.getCell(2));
//                if (pwd.equals("")) {
//                    pwd = "123456";
//                }
//                studentExample.setPassword(Md5SaltTool.getEncryptedPwd(pwd));
//                //真名
//                studentExample.setRealName(getCellValue((row.getCell(3))));
//                //电话
//                studentExample.setPhone(getCellValue(row.getCell(4)));
//                //用户表id
//                studentExample.setUser_id(Integer.parseInt(getCellValue(row.getCell(5))));
//                //行政班
//                studentExample.setClassroom_id(Integer.parseInt(getCellValue(row.getCell(6))));
//                //父母姓名
//                studentExample.setParent_name(getCellValue(row.getCell(7)));
//                //性别
//                String sex = getCellValue(row.getCell(8));
//                studentExample.setSex(sex);
//                //入学年份
//                if (!getCellValue(row.getCell(9)).equals(""))
//                    studentExample.setEnteryear(Integer.parseInt(getCellValue(row.getCell(9))));
//
//                list.add(studentExample);//把实数据放入集合里
//            }
//        }
//        return list;
//    }
//
//
////
////
//////用户表
//    public List<User> userImportExcel(MultipartFile file) throws Exception {
//        List<User> list = new ArrayList<User>();
//        String fileName = file.getOriginalFilename();
//        Workbook workbook = judgeKind(file);
//        Sheet sheet = workbook.getSheet("Sheet1");
//        int rows = sheet.getLastRowNum();//指定行数。一共多少+
//        if (rows == 0) {
//            throw new Exception("请填写行数");
//        }
//        for (int i = 1; i < rows + 1; i++) {
//            //读取左上端单元格
//            Row row = sheet.getRow(i);
//            //行不为空
//            if (row != null) {
//                //读取cell
//                User user = new User();
//                //工号
//                user.setUsername(getCellValue(row.getCell(0)));
//                //角色
//                user.setRole(getCellValue(row.getCell(1)));
//                //密码
//                String pwd = getCellValue(row.getCell(2));
//                if (pwd.equals("")) {
//                    pwd = "123456";
//                }
//               user.setPassword(Md5SaltTool.getEncryptedPwd(pwd));
//                //真名
//                user.setRealName(getCellValue((row.getCell(3))));
//                //电话
//                user.setPhone(getCellValue(row.getCell(4)));
//
//                list.add(user);//把实数据放入集合里
//            }
//        }
//        return list;
//    }
//
//    //班级
    public List<Classroom> classImportExcel(MultipartFile file) throws Exception {
        List<Classroom> list = new ArrayList<Classroom>();
        String fileName = file.getOriginalFilename();
        Workbook workbook = judgeKind(file);
        Sheet sheet = workbook.getSheet("Sheet1");
        int rows = sheet.getLastRowNum();//指定行数。一共多少+
        if (rows == 0) {
            throw new Exception("请填写行数");
        }
        for (int i = 1; i < rows + 1; i++) {
            //读取左上端单元格
            Row row = sheet.getRow(i);
            //行不为空
            if (row != null) {
                //读取cell
                Classroom classroom = new Classroom();
                //班级地址
                classroom.setRoomName(getCellValue(row.getCell(0)));
                //班级简介
                if (!getCellValue(row.getCell(1)).equals(""))
                    classroom.setMessage(getCellValue(row.getCell(1)));
                //年级
                classroom.setGrade(getCellValue(row.getCell(2)));
                //班主任id
                classroom.setTeacherId(Integer.parseInt(getCellValue(row.getCell(3))));
                //班级人数
                classroom.setClaNum(Integer.parseInt(getCellValue(row.getCell(4))));

                list.add(classroom);//把实数据放入集合里
            }
        }
        return list;
    }
}
